import express from 'express';
import xlsx from 'xlsx';
import cors from 'cors';
import CryptoJS from 'crypto-js';
import jwt from 'jsonwebtoken';
import { fileURLToPath } from 'url';
import path from 'path';
import axios from 'axios';
import mysql from 'mysql2';

const app = express();
const PORT = 15135;
const SECRET_KEY = 'my_secret_key_123';
const JWT_SECRET = 'jwt_secret_key_456';
const PASSWORD_SECRET = 'password_secret_456';

app.use(cors());
app.use(express.json());

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 创建 MySQL 连接
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '1234',
  database: 'word_memory'
});

// 连接数据库
db.connect((err) => {
  if (err) {
    console.error('数据库连接失败:', err);
    process.exit(1);
  }
  console.log('成功连接到数据库');
});

// 用户登录接口
app.post('/login', (req, res) => {
  const { username, password } = req.body;

  try {
    // 解密前端传递的加密密码
    const decryptedPassword1 = CryptoJS.AES.decrypt(password, PASSWORD_SECRET).toString(CryptoJS.enc.Utf8);

    // 查询数据库验证用户
    const query = 'SELECT * FROM admin_users WHERE username = ?';
    db.execute(query, [username], (err, results) => {
      if (err) {
        console.error('数据库查询失败:', err);
        return res.status(500).json({ success: false, message: '服务器错误' });
      }

      // 检查用户是否存在
      const user = results[0];
      if (!user) {
        return res.status(401).json({ success: false, message: '用户名或密码错误' });
      }

      // 解密数据库中的密码
      const decryptedPassword = CryptoJS.AES.decrypt(user.password, PASSWORD_SECRET).toString(CryptoJS.enc.Utf8);
      console.log(decryptedPassword, decryptedPassword1);

      // 比较前端传递的密码和数据库中的解密密码
      if (decryptedPassword !== decryptedPassword1) {
        return res.status(401).json({ success: false, message: '用户名或密码错误' });
      }

      // 检查用户角色是否是管理员
      if (user.role !== 'admin' && user.role !== 'super_admin') {
        return res.status(403).json({ success: false, message: '无权访问，只有管理员才能登录' });
      }

      // 生成 JWT Token，携带用户信息和角色
      const token = jwt.sign(
        { username: user.username, role: user.role },
        JWT_SECRET,
        { expiresIn: '2h' }
      );

      // 返回 id 字段以及 JWT Token
      res.setHeader('Authorization', `Bearer ${token}`);
      res.json({
        success: true,
        message: '管理员登录成功',
        userId: user.id,  // 返回用户id
        role: user.role    // 返回用户角色
      });
    });
  } catch (error) {
    console.error('服务器错误:', error);
    res.status(500).json({ success: false, message: '服务器错误' });
  }
});
// 修改用户状态接口
app.put('/updateUserStatus/:id', (req, res) => {
  const { id } = req.params;
  const { status } = req.body;

  // 验证参数是否有效
  if (!id || typeof status === 'undefined') {
    return res.status(400).json({ success: false, message: '用户ID和状态不能为空' });
  }

  if (![0, 1].includes(Number(status))) {
    return res.status(400).json({ success: false, message: '状态值不合法，必须为 0 或 1' });
  }

  const updateQuery = 'UPDATE users SET status = ? WHERE id = ?';
  db.execute(updateQuery, [status, id], (err, result) => {
    if (err) {
      console.error('修改用户状态失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, message: '用户不存在' });
    }

    const message = status == 1 ? '用户已启用' : '用户已封禁';
    res.json({ success: true, message });
  });
});


// 管理员注册接口
app.post('/register', (req, res) => {
  const { username, password, email } = req.body;
  const role = 'admin';
  // 检查输入的用户名、密码等字段
  if (!username || !password || !email || !role) {
    return res.status(400).json({ success: false, message: '用户名、密码、角色和邮箱不能为空' });
  }

  // 查询用户名是否已存在
  // 查询用户名是否已存在
  const checkUserQuery = 'SELECT * FROM admin_users WHERE username = ?';
  db.execute(checkUserQuery, [username], (err, results) => {
    if (err) {
      console.error('数据库查询失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (results.length > 0) {
      return res.status(400).json({ success: false, message: '用户名已存在' });
    }

    // 加密密码
    const encryptedPassword = CryptoJS.AES.encrypt(password, PASSWORD_SECRET).toString();

    // 随机生成 avatar1 - avatar5
    const avatarNumber = Math.floor(Math.random() * 5) + 1; // 1 到 5
    const avatar = `avatar${avatarNumber}`;

    // 插入管理员用户到数据库
    const insertQuery = 'INSERT INTO admin_users (username, password, role, email, avatar) VALUES (?, ?, ?, ?, ?)';
    db.execute(insertQuery, [username, encryptedPassword, role, email, avatar], (err, result) => {
      if (err) {
        console.error('管理员注册失败:', err);
        return res.status(500).json({ success: false, message: '服务器错误' });
      }

      res.json({ success: true, message: '管理员注册成功' });
    });
  });

});
// 删除管理员用户接口
// 根据 ID 删除管理员用户
app.delete('/deleteAdminUser/:id', (req, res) => {
  const { id } = req.params;

  if (!id) {
    return res.status(400).json({ success: false, message: '用户ID不能为空' });
  }

  const deleteQuery = 'DELETE FROM admin_users WHERE id = ?';
  db.execute(deleteQuery, [id], (err, result) => {
    if (err) {
      console.error('删除管理员失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, message: '未找到该用户' });
    }

    res.json({ success: true, message: '管理员删除成功' });
  });
});

app.delete('/deleteUser/:id', (req, res) => {
  const { id } = req.params;

  if (!id) {
    return res.status(400).json({ success: false, message: '用户ID不能为空' });
  }

  const deleteQuery = 'DELETE FROM users WHERE id = ?';
  db.execute(deleteQuery, [id], (err, result) => {
    if (err) {
      console.error('删除普通用户失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, message: '未找到该用户' });
    }

    res.json({ success: true, message: '用户删除成功' });
  });
});

// 查询所有用户的登录日志
app.get('/logs', (req, res) => {
  const query = `
    SELECT log_id, user_id, login_time, logout_time, login_ip, user_agent, login_status, failed_attempts
    FROM user_login_log
    ORDER BY login_time DESC
  `;

  db.execute(query, [], (err, results) => {
    if (err) {
      console.error('查询所有登录日志失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({ success: true, logs: results });
  });
});
// 获取所有学习日志接口
// 获取所有学习日志接口
app.get('/getAllStudyLogs', (req, res) => {
  const query = `
    SELECT log_id, user_id, study_date, start_time, end_time, duration_minutes, word_count, session_type
    FROM user_study_log
    ORDER BY study_date DESC, start_time DESC
  `;

  db.execute(query, [], (err, results) => {
    if (err) {
      console.error('获取所有学习日志失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({
      success: true,
      message: '获取所有学习日志成功',
      logs: results
    });
  });
});
// 获取五条记录
app.get('/api/recent-study-logs', (req, res) => {
  const query = `
    SELECT 
      l.log_id,
      l.user_id,
      l.study_date,
      l.start_time,
      l.end_time,
      l.duration_minutes,
      l.word_count,
      l.session_type,
      u.username,
      u.email,
      u.avatar
    FROM user_study_log l
    JOIN users u ON l.user_id = u.id
    ORDER BY l.study_date DESC, l.start_time DESC
    LIMIT 5
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error('获取学习日志失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({
      success: true,
      message: '获取最近5条学习日志成功',
      logs: results
    });
  });
});

// 根据文章ID删除文章
app.delete('/admin/api/articles/:articleId', (req, res) => {
  const { articleId } = req.params;

  const sql = `
    DELETE FROM forum_articles
    WHERE id = ?
  `;

  db.query(sql, [articleId], (err, result) => {
    if (err) {
      console.error('删除文章失败:', err);
      return res.status(500).json({ error: '数据库操作出错' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: '未找到对应的文章' });
    }

    res.json({ success: true, message: '文章删除成功' });
  });
});



// 获取用户列表接口
app.get('/users', (req, res) => {
  const userId = req.query.userId;  // 从请求参数中获取用户ID

  if (!userId) {
    return res.status(400).send('User ID is required');
  }

  // 查询 admin_users 表，根据 userId 获取角色信息
  const getUserRoleQuery = 'SELECT role FROM admin_users WHERE id = ?';

  db.query(getUserRoleQuery, [userId], (err, results) => {
    if (err) {
      console.error('查询角色失败:', err);
      return res.status(500).send('Database query error');
    }

    if (results.length === 0) {
      return res.status(404).send('User not found');
    }

    const userRole = results[0].role;  // 获取角色信息
    // console.log(userRole, 'userRole');
    let query = '';
    if (userRole === 'super_admin') {
      // 超级管理员可以查询 users 和 admin_users 两张表
      // 这里分别执行两个查询
      const usersQuery = 'SELECT * FROM users;';
      const adminUsersQuery = 'SELECT * FROM admin_users;';

      // 执行第一个查询 (查询 users)
      db.query(usersQuery, (err, usersResults) => {
        if (err) {
          console.error('查询 users 表失败:', err);
          return res.status(500).send('Database query error');
        }

        // 执行第二个查询 (查询 admin_users)
        db.query(adminUsersQuery, (err, adminUsersResults) => {
          if (err) {
            console.error('查询 admin_users 表失败:', err);
            return res.status(500).send('Database query error');
          }

          // 返回两个查询的结果
          return res.json({
            users: usersResults,
            admin_users: adminUsersResults
          });
        });
      });
    } else if (userRole === 'admin') {
      // 普通管理员只能查询 users 表
      const usersQuery = 'SELECT * FROM users;';

      db.query(usersQuery, (err, usersResults) => {
        if (err) {
          console.error('查询 users 表失败:', err);
          return res.status(500).send('Database query error');
        }

        return res.json({
          users: usersResults
        });
      });
    } else {
      return res.status(403).send('Permission denied');
    }
  });
});

// 获取管理员数据接口
app.get('/admins/:id', (req, res) => {
  const adminId = req.params.id;

  // 查询数据库中的管理员信息
  const query = 'SELECT id, username, email, avatar, role, created_at FROM admin_users WHERE id = ?';

  db.query(query, [adminId], (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '服务器错误' });
    }

    if (results.length === 0) {
      return res.status(404).json({ message: '管理员未找到' });
    }

    // 返回管理员数据
    res.status(200).json(results[0]);
  });
});


// 获取单词数据的 API 路由
app.get('/getWordsData', (req, res) => {
  const query = 'SELECT * FROM words';

  db.query(query, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ message: '查询失败' });
    }

    res.status(200).json({
      message: '获取单词数据成功',
      data: results // 返回查询到的所有单词数据
    });
  });
});

// 删除单词数据的 API 路由
app.delete('/api/words/:id', (req, res) => {
  const wordId = req.params.id;

  const sql = 'DELETE FROM words WHERE id = ?';
  db.query(sql, [wordId], (err, result) => {
    if (err) {
      console.error('删除单词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到该单词' });
    }

    res.json({ message: '单词删除成功', deletedId: wordId });
  });
});

// 更新单词数据的 API 路由
app.put('/api/update-word-by-id/:id', (req, res) => {
  const wordId = req.params.id;
  const { word, definition, phonetic } = req.body;

  if (!word || word.trim() === '') {
    return res.status(400).json({ message: '单词不能为空' });
  }

  const sql = `
    UPDATE words 
    SET word = ?, definition = ?, phonetic = ?
    WHERE id = ?;
  `;

  db.query(sql, [word, definition, phonetic, wordId], (err, result) => {
    if (err) {
      console.error('更新单词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到对应的单词 ID' });
    }

    res.json({ message: '单词更新成功' });
  });
});


// 删除例句的接口
app.delete('/api/word-sentences/:id', (req, res) => {
  const sentenceId = req.params.id;

  const sql = 'DELETE FROM word_sentences WHERE id = ?';

  db.query(sql, [sentenceId], (err, result) => {
    if (err) {
      console.error('删除例句失败:', err);
      return res.status(500).json({ message: '服务器错误，删除失败' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '找不到该例句 ID' });
    }

    res.json({ message: '例句删除成功' });
  });
});

// 添加例句接口（推荐语义）
app.post('/api/words/:wordId/sentences', (req, res) => {
  const wordId = req.params.wordId;
  const { sentence, translation } = req.body;

  if (!sentence) {
    return res.status(400).json({ message: '例句内容不能为空' });
  }

  const sql = `
    INSERT INTO word_sentences (word_id, sentence, translation)
    VALUES (?, ?, ?)
  `;

  db.query(sql, [wordId, sentence, translation], (err, result) => {
    if (err) {
      console.error('添加例句失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }
    res.json({ message: '例句添加成功', sentenceId: result.insertId });
  });
});
// 更新例句接口（根据句子 ID）
app.put('/api/UpdataWord-sentences/:id', (req, res) => {
  const sentenceId = req.params.id;
  const { sentence, translation } = req.body;

  if (!sentence) {
    return res.status(400).json({ message: '例句内容不能为空' });
  }

  const sql = `
    UPDATE word_sentences
    SET sentence = ?, translation = ?
    WHERE id = ?
  `;

  db.query(sql, [sentence, translation, sentenceId], (err, result) => {
    if (err) {
      console.error('更新例句失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '例句未找到' });
    }

    res.json({ message: '例句更新成功' });
  });
});
// 更新同义词的接口
app.put('/api/update-word-synonyms/:id', (req, res) => {
  const synonymId = req.params.id;
  const synonym = req.body;
  // console.log(synonym);
  if (!synonym) {
    return res.status(400).json({ message: '同义词数据不能为空' });
  }

  const sql = `
    UPDATE word_synonyms
    SET synonym = ?, created_at = NOW()
    WHERE id = ?
  `;

  db.query(sql, [JSON.stringify(synonym), synonymId], (err, result) => {
    if (err) {
      console.error('更新同义词失败:', err);
      return res.status(500).json({ message: '服务器内部错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到对应的同义词记录' });
    }

    res.json({ message: '同义词更新成功' });
  });
});

// 更新反义词接口
app.put('/api/update-word-antonyms/:id', (req, res) => {
  const antonymId = req.params.id;
  const antonym = req.body;

  if (!antonym) {
    return res.status(400).json({ message: '缺少反义词数据' });
  }

  const sql = 'UPDATE word_antonyms SET antonym = ?, created_at = NOW() WHERE id = ?';
  const values = [JSON.stringify(antonym), antonymId];

  db.query(sql, values, (err, result) => {
    if (err) {
      console.error('更新反义词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到对应的反义词记录' });
    }

    res.json({ message: '反义词更新成功' });
  });
});



// 学习新单词
app.post('/get-unlearned-words', (req, res) => {
  const user_id = parseInt(req.body.user_id, 10);
  const limit = parseInt(req.body.limit, 10);

  // 确保参数有效
  if (isNaN(user_id) || isNaN(limit)) {
    return res.status(400).json({ success: false, message: 'Invalid input parameters' });
  }

  // 拼接 LIMIT 参数
  const query = `
    SELECT w.id AS word_id, w.word, w.definition, w.phonetic
    FROM words w
    LEFT JOIN user_words uw ON w.id = uw.word_id AND uw.user_id = ?
    WHERE uw.word_id IS NULL
    LIMIT ?;
  `;

  // 使用 query 方法执行查询
  db.query(query, [user_id, limit], (err, results) => {
    if (err) {
      console.error('数据库查询失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({ success: true, words: results });
  });
});


// 插入学习的单词
app.post('/insert-user-words', (req, res) => {
  const { userId, learnedWords } = req.body;  // 前端传递的用户ID和学习过的单词数组
  console.log(userId, learnedWords);

  // 输入验证
  if (!userId || !learnedWords || learnedWords.length === 0) {
    return res.status(400).json({ success: false, message: '无效的输入数据' });
  }

  // 确保 userId 是有效的整数
  const validUserId = parseInt(userId, 10);
  if (isNaN(validUserId)) {
    return res.status(400).json({ success: false, message: '无效的用户ID' });
  }

  // 当前时间，MySQL 兼容格式
  const currentTime = new Date().toISOString().slice(0, 19).replace('T', ' '); // 转换为 'YYYY-MM-DD HH:MM:SS'
  const nextStudyTime = new Date(new Date().getTime() + 24 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');  // 默认下一次学习时间为 24 小时后

  // 准备批量插入的数据
  const insertData = learnedWords.map(word => {
    return [
      validUserId,  // 用户ID
      word.word_id,  // 单词ID
      currentTime,  // 上次学习时间
      nextStudyTime,  // 下次学习时间
      word.proficiency || 0,  // 学习熟练度，默认为 0
    ];
  });

  console.log(insertData);

  // SQL 插入语句
  const query = `
    INSERT INTO user_words (user_id, word_id, last_study_time, next_study_time, proficiency)
    VALUES ?
  `;

  db.query(query, [insertData], (err, result) => {
    if (err) {
      console.error('批量插入学习记录失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({ success: true, message: '学习记录插入成功' });
  });
});



// 查询用户学习统计信息
app.get('/user-stats/:user_id', (req, res) => {
  const user_id = parseInt(req.params.user_id, 10);

  // 确保参数有效
  if (isNaN(user_id)) {
    return res.status(400).json({ success: false, message: 'Invalid user ID' });
  }

  const query = `
    SELECT 
      u.id AS user_id,
      u.username,
      (SELECT COUNT(*) FROM words) AS total_words,
      (SELECT COUNT(*) FROM user_words WHERE user_id = ?) AS learned_words,
      (SELECT COUNT(*) FROM words) - (SELECT COUNT(*) FROM user_words WHERE user_id = ?) AS unlearned_words
    FROM 
      users u
    WHERE 
      u.id = ?;
  `;

  // 使用 query 方法执行查询
  db.query(query, [user_id, user_id, user_id], (err, results) => {
    if (err) {
      console.error('数据库查询失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (results.length === 0) {
      return res.status(404).json({ success: false, message: '用户不存在' });
    }

    res.json({
      success: true,
      stats: {
        user_id: results[0].user_id,
        username: results[0].username,
        total_words: results[0].total_words,
        learned_words: results[0].learned_words,
        unlearned_words: results[0].unlearned_words,
        learning_progress: (results[0].learned_words / results[0].total_words * 100).toFixed(2) + '%'
      }
    });
  });
});


// 插入笔记记录
app.post('/addNote', (req, res) => {
  let { user_id, word_id, note } = req.body;

  // 校验字段
  if (!user_id || !word_id || !note) {
    return res.status(400).send('缺少必要的字段');
  }

  // 转换 ID 为整数
  user_id = parseInt(user_id, 10);
  word_id = parseInt(word_id, 10);
  console.log(`收到请求：user_id=${user_id}, word_id=${word_id}, note=${note}`);

  // 检查转换是否有效
  if (isNaN(user_id) || isNaN(word_id)) {
    return res.status(400).send('user_id 或 word_id 必须是有效的整数');
  }

  // 插入笔记，同时设置 updated_at 为当前时间
  const query = `
    INSERT INTO notes (user_id, word_id, note, updated_at)
    VALUES (?, ?, ?, NOW())
  `;

  db.query(query, [user_id, word_id, note], (err, result) => {
    if (err) {
      console.error('插入笔记失败:', err);
      return res.status(500).send('服务器错误');
    }
    res.status(200).send('笔记添加成功');
  });
});

// 查询用户单词笔记记录
app.get('/getNotes', (req, res) => {
  let { user_id, word_id } = req.query;

  // 确保 user_id 和 word_id 都已提供
  if (!user_id || !word_id) {
    return res.status(400).send('缺少必要的字段');
  }

  // 将 user_id 和 word_id 转换为整数
  user_id = parseInt(user_id, 10);
  word_id = parseInt(word_id, 10);
  // console.log(`收到请求：user_id=${user_id}, word_id=${word_id}`);

  // 检查转换是否成功
  if (isNaN(user_id) || isNaN(word_id)) {
    return res.status(400).send('user_id 或 word_id 必须是有效的整数');
  }

  const query = `
    SELECT n.id, n.user_id, n.word_id, n.note, n.created_at, n.updated_at
    FROM notes n
    WHERE n.user_id = ? AND n.word_id = ?;
  `;

  db.query(query, [user_id, word_id], (err, result) => {
    if (err) {
      console.error('查询笔记失败:', err);
      return res.status(500).send('服务器错误');
    }

    if (result.length === 0) {
      return res.status(404).send('没有找到该单词的笔记');
    }

    res.status(200).json(result);
  });
});
// 删除笔记
app.delete('/deleteNote/:note_id', (req, res) => {
  const { note_id } = req.params;
  // console.log(note_id);

  if (!note_id) {
    return res.status(400).json({ message: '缺少 note_id 参数' });
  }

  const query = 'DELETE FROM notes WHERE id = ?';
  db.query(query, [note_id], (err, result) => {
    if (err) {
      console.error('删除笔记失败:', err);
      return res.status(500).json({ message: '服务器错误，删除笔记失败' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到要删除的笔记' });
    }

    res.json({ message: '笔记删除成功' });
  });
});
// 编辑笔记接口
app.put('/editNote/:note_id', (req, res) => {
  const { note_id } = req.params;
  const { note } = req.body;
  console.log(note_id, note, 'xsacascaca');

  if (!note_id || !note) {
    return res.status(400).json({ message: '缺少 note_id 或 note 内容' });
  }

  const query = 'UPDATE notes SET note = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?';
  db.query(query, [note, note_id], (err, result) => {
    if (err) {
      console.error('更新笔记失败:', err);
      return res.status(500).json({ message: '服务器错误，更新失败' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到该笔记' });
    }

    res.json({ message: '笔记更新成功' });
  });
});

//用户签到接口（签到打卡）
app.post('/signin', (req, res) => {
  const { user_id } = req.body;

  if (!user_id) {
    return res.status(400).json({ message: '缺少 user_id' });
  }

  const today = new Date().toISOString().slice(0, 10); // 获取 yyyy-mm-dd 格式

  // 检查今天是否已经签到
  const checkQuery = 'SELECT * FROM user_signins WHERE user_id = ? AND signin_date = ?';
  db.query(checkQuery, [user_id, today], (err, results) => {
    if (err) {
      console.error('查询签到记录失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (results.length > 0) {
      return res.status(400).json({ message: '今天已经签到过了' });
    }

    // 插入签到记录
    const insertQuery = 'INSERT INTO user_signins (user_id, signin_date) VALUES (?, ?)';
    db.query(insertQuery, [user_id, today], (err, result) => {
      if (err) {
        console.error('签到失败:', err);
        return res.status(500).json({ message: '服务器错误，签到失败' });
      }

      res.json({ message: '签到成功', id: result.insertId });
    });
  });
});
//查询用户签到记录接口
app.get('/signin-records/:user_id', (req, res) => {
  const { user_id } = req.params;

  const query = 'SELECT signin_date, signin_time FROM user_signins WHERE user_id = ? ORDER BY signin_date DESC';
  db.query(query, [user_id], (err, results) => {
    if (err) {
      console.error('获取签到记录失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({ message: '查询成功', records: results });
  });
});

// 获取需要复习的单词
// 获取需要复习的单词（支持限制返回数量）
app.get('/review-words', (req, res) => {
  const userId = parseInt(req.query.user_id, 10);
  const limit = parseInt(req.query.limit, 10) || 10; // 默认一次拉取10个

  // console.log(userId,limit);



  if (isNaN(userId) || userId <= 0) {
    return res.status(400).json({ message: '无效的用户ID' });
  }

  const query = `
    SELECT w.id, w.word, w.definition, w.phonetic, uw.proficiency, uw.next_study_time
    FROM user_words uw
    JOIN words w ON uw.word_id = w.id
    WHERE uw.user_id = ? AND uw.next_study_time <= NOW()
    ORDER BY uw.next_study_time ASC
    LIMIT ?
  `;

  db.query(query, [userId, limit], (err, results) => {
    if (err) {
      console.error('查询需要复习的单词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({ success: true, words: results });
    // console.log(results);

  });
});
// 获取当前需要复习的单词总数
app.get('/review-word-count', (req, res) => {
  const userId = parseInt(req.query.user_id, 10);

  if (isNaN(userId) || userId <= 0) {
    return res.status(400).json({ message: '无效的用户ID' });
  }

  const query = `
    SELECT COUNT(*) AS count
    FROM user_words
    WHERE user_id = ? AND next_study_time <= NOW()
  `;

  db.query(query, [userId], (err, results) => {
    if (err) {
      console.error('统计需要复习的单词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({ success: true, count: results[0].count });
  });
});



// 批量更新单词的复习时间
// update-review-time
app.post('/update-review-time', (req, res) => {
  const { userId, words } = req.body;
  const user_id = parseInt(userId);
  // console.log('user_id:', user_id, words);

  if (!user_id || !Array.isArray(words)) {
    return res.status(400).json({ message: '参数缺失或格式错误' });
  }

  const reviewDaysMapping = [1, 3, 7, 14, 30, 50, 90];

  const updates = words.map(word => {
    return new Promise((resolve, reject) => {
      const { id: word_id, proficiency } = word;

      const newProficiency = Math.min(proficiency + 1, reviewDaysMapping.length - 1);
      const daysToAdd = reviewDaysMapping[proficiency]; // 用旧的 proficiency 算复习时间
      const nextStudyTime = new Date();
      nextStudyTime.setDate(nextStudyTime.getDate() + daysToAdd);

      const sql = `
        UPDATE user_words 
        SET next_study_time = ?, proficiency = ?
        WHERE user_id = ? AND word_id = ?
      `;
      db.query(sql, [nextStudyTime, newProficiency, user_id, word_id], (err, result) => {
        if (err) {
          console.error(`更新单词 ${word_id} 时出错:`, err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  });

  Promise.all(updates)
    .then(() => {
      res.json({ message: '单词复习信息更新成功（按旧熟练度定时间，熟练度+1）' });
    })
    .catch((err) => {
      res.status(500).json({ message: '更新过程中出错', error: err });
    });
});

// 处理论坛插入文章的接口
app.post('/api/forum_articles', (req, res) => {
  const { title, word, excerpt, category, votes, comments, user_id } = req.body;

  // 确保数据的完整性（必要字段检查）
  if (!title || !word || !category || !user_id) {
    return res.status(400).json({ message: '缺少必要的字段' });
  }

  // 文章插入SQL语句
  const sql = `
    INSERT INTO forum_articles (title, word, excerpt, category, votes, comments, date, user_id)
    VALUES (?, ?, ?, ?, ?, ?, NOW(), ?)
  `;

  // 执行插入操作
  db.execute(sql, [title, word, excerpt, category, votes || 0, comments || 0, user_id], (err, result) => {
    if (err) {
      console.error('插入文章失败:', err);
      return res.status(500).json({ message: '插入失败', error: err });
    }

    // 插入成功，返回文章ID
    return res.status(201).json({
      message: '文章插入成功',
      articleId: result.insertId
    });
  });
});
// 获取所有论坛文章的接口
// 获取所有论坛文章的接口
// app.get('/api/forum_articles', (req, res) => {
//   const query = `
//     SELECT fa.id, fa.title, fa.word, fa.excerpt, fa.category, fa.votes, fa.comments, fa.date, fa.user_id, u.username AS author_name
//     FROM forum_articles fa
//     JOIN users u ON fa.user_id = u.id
//   `;

//   db.query(query, (err, results) => {
//     if (err) {
//       console.error('查询失败:', err);
//       return res.status(500).json({ error: '查询失败' });
//     }

//     // 返回查询结果
//     res.json(results);
//     // console.log(results);  // 打印查询结果，检查数据
//   });
// });

app.get('/api/forum_articles', (req, res) => {
  const query = `
    SELECT fa.id, fa.title, fa.word, fa.excerpt, fa.category, fa.votes, fa.comments, fa.date, fa.user_id, 
           u.username AS author_name, u.avatar AS author_avatar
    FROM forum_articles fa
    JOIN users u ON fa.user_id = u.id
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '查询失败' });
    }

    // 返回查询结果，包含用户头像
    res.json(results);
  });
});


// 处理插入评论的接口
app.post('/api/article_comments', (req, res) => {
  const { article_id, user_id, comment } = req.body;

  // 确保数据的完整性（必要字段检查）
  if (!article_id || !user_id || !comment) {
    return res.status(400).json({ message: '缺少必要的字段' });
  }

  // 评论插入SQL语句
  const sql = `
    INSERT INTO article_comments (article_id, user_id, comment, created_at)
    VALUES (?, ?, ?, NOW())
  `;

  // 执行插入操作
  db.execute(sql, [article_id, user_id, comment], (err, result) => {
    if (err) {
      console.error('插入评论失败:', err);
      return res.status(500).json({ message: '插入失败', error: err });
    }

    // 插入成功，返回评论ID
    return res.status(201).json({
      message: '评论插入成功',
      commentId: result.insertId
    });
  });
});
// 处理获取文章评论的接口
app.get('/api/article_comments/:article_id', (req, res) => {
  const { article_id } = req.params;

  const sql = `
    SELECT ac.id, ac.comment, ac.created_at, u.username
    FROM article_comments ac
    JOIN users u ON ac.user_id = u.id
    WHERE ac.article_id = ?
    ORDER BY ac.created_at DESC
  `;

  db.execute(sql, [article_id], (err, result) => {
    if (err) {
      console.error('获取评论失败:', err);
      return res.status(500).json({ message: '获取失败', error: err });
    }

    return res.status(200).json({
      message: '评论获取成功',
      comments: result
    });
  });
});
// 处理获取论坛文章的接口
app.get('/admin/api/forum_articles', (req, res) => {
  const query = `
    SELECT fa.id, fa.title, fa.word, fa.excerpt, fa.category, fa.votes, fa.comments, fa.date, fa.content,
           fa.user_id, u.username AS author_name, u.avatar AS author_avatar
    FROM forum_articles fa
    JOIN users u ON fa.user_id = u.id
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '查询失败' });
    }

    // 返回查询结果，包含用户头像和文章内容
    res.json(results);
  });
});
// 🚀 获取单词的例句接口
// app.get('/api/sentences/:word', (req, res) => {
//   const word = req.params.word;

//   const query = `
//     SELECT ws.sentence, ws.translation
//     FROM word_sentences ws
//     JOIN words w ON ws.word_id = w.id
//     WHERE w.word = ?
//   `;

//   db.query(query, [word], (err, results) => {
//     if (err) {
//       console.error('查询失败:', err);
//       return res.status(500).json({ error: '数据库查询失败' });
//     }

//     res.json({
//       word,
//       sentences: results
//     });
//   });
// });
// 获取某个单词的例句列表
app.get('/api/words/:wordId/sentences', (req, res) => {
  const wordId = req.params.wordId;

  const query = 'SELECT id, sentence, translation FROM word_sentences WHERE word_id = ?';

  db.query(query, [wordId], (err, results) => {
    if (err) {
      console.error('获取例句失败:', err);
      return res.status(500).json({ error: '获取例句失败' });
    }

    res.json({ sentences: results });
  });
});
// 获取全部例句
app.get('/api/sentences', (req, res) => {
  const query = 'SELECT id, word_id, sentence, translation FROM word_sentences';

  db.query(query, (err, results) => {
    if (err) {
      console.error('获取全部例句失败:', err);
      return res.status(500).json({ error: '获取全部例句失败' });
    }

    res.json({ sentences: results });
  });
});
// 查询某个单词的同义词和反义词
app.get('/api/words/:wordId/synonyms-antonyms', (req, res) => {
  const wordId = req.params.wordId;
  // console.log('请求的 wordId:', wordId);

  const synonymQuery = 'SELECT synonym FROM word_synonyms WHERE word_id = ?';
  const antonymQuery = 'SELECT antonym FROM word_antonyms WHERE word_id = ?';

  // 并行查询同义词和反义词
  Promise.all([
    new Promise((resolve, reject) => {
      db.query(synonymQuery, [wordId], (err, results) => {
        if (err) {
          console.error('查询同义词失败:', err);
          reject(err);
        } else {
          // 直接返回 JSON 字符串，无需解析
          const synonymData = results[0]?.synonym || '[]';  // 如果没有同义词，返回一个空数组的 JSON 字符串
          resolve(synonymData);
        }
      });
    }),
    new Promise((resolve, reject) => {
      db.query(antonymQuery, [wordId], (err, results) => {
        if (err) {
          console.error('查询反义词失败:', err);
          reject(err);
        } else {
          // 直接返回 JSON 字符串，无需解析
          const antonymData = results[0]?.antonym || '[]';  // 如果没有反义词，返回一个空数组的 JSON 字符串
          resolve(antonymData);
        }
      });
    })
  ])
    .then(([synonyms, antonyms]) => {
      // 返回同义词和反义词的 JSON 字符串
      res.json({ synonyms, antonyms });
    })
    .catch((err) => {
      console.error('❌ 获取同义词/反义词失败:', err);
      res.status(500).json({ error: '查询失败' });
    });
});

// 获取单词的所有同义词和反义词
// 获取所有同义词和反义词原始记录
app.get('/api/words/all-synonyms-antonyms', (req, res) => {
  const synonymQuery = 'SELECT * FROM word_synonyms';
  const antonymQuery = 'SELECT * FROM word_antonyms';

  Promise.all([
    new Promise((resolve, reject) => {
      db.query(synonymQuery, (err, results) => {
        if (err) {
          console.error('查询同义词失败:', err);
          reject(err);
        } else {
          resolve(results); // 不处理，直接返回原始数组
        }
      });
    }),
    new Promise((resolve, reject) => {
      db.query(antonymQuery, (err, results) => {
        if (err) {
          console.error('查询反义词失败:', err);
          reject(err);
        } else {
          resolve(results); // 不处理，直接返回原始数组
        }
      });
    })
  ])
    .then(([synonyms, antonyms]) => {
      res.json({ synonyms, antonyms }); // 直接返回完整数组
    })
    .catch(err => {
      console.error('❌ 获取全部同义词/反义词失败:', err);
      res.status(500).json({ error: '查询失败' });
    });
});



































// .......................................................................
// 仪表盘数据.
// .....................................................................



// 获取用户增长数据
app.get('/api/user-growth', (req, res) => {
  const todayTotalQuery = `SELECT COUNT(*) AS total FROM users`;
  const yesterdayTotalQuery = `SELECT COUNT(*) AS total FROM users WHERE created_at < CURDATE()`;

  db.query(todayTotalQuery, (err, todayResult) => {
    if (err) return res.status(500).json({ error: '获取今日总用户数失败' });

    db.query(yesterdayTotalQuery, (err, yesterdayResult) => {
      if (err) return res.status(500).json({ error: '获取昨日用户总数失败' });

      const todayTotal = todayResult[0].total;
      const yesterdayTotal = yesterdayResult[0].total;

      const growthRate =
        yesterdayTotal > 0
          ? (((todayTotal - yesterdayTotal) / yesterdayTotal) * 100).toFixed(2)
          : '0.00';

      res.json({
        todayTotal,
        yesterdayTotal,
        growthRate: growthRate + '%'
      });
    });
  });
});

// 获取用户活跃增长数据
app.get('/api/signin-growth', (req, res) => {
  const sql = `
    SELECT
      (SELECT COUNT(DISTINCT user_id)
       FROM user_signins
       WHERE signin_date = CURDATE()) AS todaySignins,

      (SELECT COUNT(DISTINCT user_id)
       FROM user_signins
       WHERE signin_date = CURDATE() - INTERVAL 1 DAY) AS yesterdaySignins
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ message: '查询失败' });
    }

    const { todaySignins, yesterdaySignins } = results[0];

    let growthRate = 0;
    if (yesterdaySignins > 0) {
      growthRate = ((todaySignins - yesterdaySignins) / yesterdaySignins) * 100;
    } else if (todaySignins > 0) {
      growthRate = 100;
    }

    res.json({
      todaySignins,
      yesterdaySignins,
      growthRate: growthRate.toFixed(2) + '%'
    });
  });
});

// 获取学习增长数据
app.get('/api/study-growth', (req, res) => {
  const sql = `
    SELECT
      SUM(CASE WHEN study_date = CURDATE() THEN 1 ELSE 0 END) AS todayCount,
      SUM(CASE WHEN study_date = CURDATE() - INTERVAL 1 DAY THEN 1 ELSE 0 END) AS yesterdayCount
    FROM user_study_log;
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    const today = results[0].todayCount || 0;
    const yesterday = results[0].yesterdayCount || 0;

    let growthRate = 0;
    if (yesterday === 0 && today > 0) {
      growthRate = 100;
    } else if (yesterday === 0 && today === 0) {
      growthRate = 0;
    } else {
      growthRate = ((today - yesterday) / yesterday) * 100;
    }

    res.json({
      todayStudyLogs: today,
      yesterdayStudyLogs: yesterday,
      growthRate: growthRate.toFixed(2) + '%'
    });
  });
});

// 获取新学习与复习数据及增长率
app.get('/api/study-type-growth', (req, res) => {
  const sql = `
    SELECT
      SUM(CASE WHEN study_date = CURDATE() AND session_type = 'study' THEN 1 ELSE 0 END) AS todayNew,
      SUM(CASE WHEN study_date = CURDATE() AND session_type = 'review' THEN 1 ELSE 0 END) AS todayReview,
      SUM(CASE WHEN study_date = CURDATE() - INTERVAL 1 DAY AND session_type = 'study' THEN 1 ELSE 0 END) AS yesterdayNew,
      SUM(CASE WHEN study_date = CURDATE() - INTERVAL 1 DAY AND session_type = 'review' THEN 1 ELSE 0 END) AS yesterdayReview
    FROM user_study_log;
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    const row = results[0];
    // 默认值设置为 0，避免 undefined
    const todayNew = Number(row.todayNew || 0);
    const todayReview = Number(row.todayReview || 0);
    const yesterdayNew = Number(row.yesterdayNew || 0);
    const yesterdayReview = Number(row.yesterdayReview || 0);

    // 计算增长率的函数，确保输入都是数字
    const calcGrowth = (today, yesterday) => {
      if (isNaN(today) || isNaN(yesterday)) return 0;  // 如果输入不是数字，返回0
      if (yesterday === 0 && today > 0) return 100;
      if (yesterday === 0 && today === 0) return 0;
      return ((today - yesterday) / yesterday) * 100;
    };

    const newGrowthRate = `${calcGrowth(todayNew, yesterdayNew).toFixed(2)}%`;
    const reviewGrowthRate = `${calcGrowth(todayReview, yesterdayReview).toFixed(2)}%`;

    // console.log(todayNew, todayReview, yesterdayNew, yesterdayReview, newGrowthRate, reviewGrowthRate);

    res.json({
      todayNew,
      todayReview,
      yesterdayNew,
      yesterdayReview,
      newGrowthRate,
      reviewGrowthRate
    });
  });
});




// 获取今日与昨日学习时间及增长比率的接口
// 获取今日与昨日学习时间及增长比率的接口
app.get('/api/study-growth-today-vs-yesterday', (req, res) => {
  // 查询今日的学习时长
  const queryToday = 'SELECT SUM(duration_minutes) AS total_today, CURDATE() AS study_date_today FROM user_study_log WHERE study_date = CURDATE()';
  // 查询昨日的学习时长
  const queryYesterday = 'SELECT SUM(duration_minutes) AS total_yesterday, CURDATE() - INTERVAL 1 DAY AS study_date_yesterday FROM user_study_log WHERE study_date = CURDATE() - INTERVAL 1 DAY';

  db.query(queryToday, (err, todayResult) => {
    if (err) return res.status(500).json({ message: '获取今日学习数据失败' });
    const totalToday = todayResult[0].total_today || 0;
    const studyDateToday = todayResult[0].study_date_today;

    db.query(queryYesterday, (err, yesterdayResult) => {
      if (err) return res.status(500).json({ message: '获取昨日学习数据失败' });
      const totalYesterday = yesterdayResult[0].total_yesterday || 0;
      const studyDateYesterday = yesterdayResult[0].study_date_yesterday;

      let growthRate = 0;
      if (totalYesterday > 0) {
        growthRate = ((totalToday - totalYesterday) / totalYesterday) * 100;
      }

      return res.json({
        todayTotal: totalToday,
        yesterdayTotal: totalYesterday,
        growthRate: growthRate.toFixed(2) + '%',
        studyDateToday,  // 今日日期
        studyDateYesterday // 昨日日期
      });
    });
  });
});
// 获取错误次数最多的单词及其错误次数总和
app.get('/api/error-words-statistics/most-error-words', (req, res) => {
  // 查询按单词统计错误次数，并按照错误次数从高到低排序
  const query = `
    SELECT ws.word_id, w.word, SUM(ws.error_count) AS total_error_count
    FROM error_words_statistics ws
    JOIN words w ON ws.word_id = w.id
    GROUP BY ws.word_id
    ORDER BY total_error_count DESC
    LIMIT 1;  -- 获取错误次数最多的单词
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error('查询错误次数最多的单词失败:', err);
      return res.status(500).json({ message: '获取数据失败' });
    }

    // 如果没有数据
    if (results.length === 0) {
      return res.status(404).json({ message: '没有找到数据' });
    }

    // 返回结果
    const mostErrorWord = results[0];
    return res.json({
      wordId: mostErrorWord.word_id,
      word: mostErrorWord.word,
      totalErrorCount: mostErrorWord.total_error_count
    });
  });
});


// 获取单词总数和例句总数
app.get('/api/word-and-sentence-stats', (req, res) => {
  const sql = `
    SELECT 
      (SELECT COUNT(*) FROM words) AS wordCount,
      (SELECT COUNT(*) FROM word_sentences) AS sentenceCount;
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('查询单词和例句数量失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    const wordCount = results[0].wordCount || 0;
    const sentenceCount = results[0].sentenceCount || 0;

    res.json({
      wordCount,
      sentenceCount
    });
  });
});
// 获取最近7天每天的用户签到数（使用中国时间）
app.get('/api/signin-stats-last-7-days', (req, res) => {
  const sql = `
    SELECT 
      signin_date,
      COUNT(DISTINCT user_id) AS userCount
    FROM user_signins
    WHERE signin_date >= CURDATE() - INTERVAL 6 DAY
    GROUP BY signin_date
    ORDER BY signin_date ASC;
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('查询签到统计失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    // console.log('📦 数据库原始结果:', results);

    // 构建结果映射表
    const statsMap = {};
    results.forEach(row => {
      // 将 UTC 转为中国时间（UTC+8）
      const localDate = new Date(row.signin_date);
      localDate.setHours(localDate.getHours() + 8);
      const dateKey = localDate.toISOString().slice(0, 10); // 格式化为 'YYYY-MM-DD'
      // console.log(`🔧 映射 ${dateKey} => ${row.userCount}`);
      statsMap[dateKey] = row.userCount;
    });

    // 构建完整7天数据（确保每天都有）
    const today = new Date();
    const dateList = [];

    for (let i = 6; i >= 0; i--) {
      const d = new Date(today);
      d.setDate(today.getDate() - i);
      const key = d.toISOString().slice(0, 10);

      dateList.push({
        date: key,
        userCount: statsMap[key] || 0
      });
    }

    // console.log('✅ 最终输出:', dateList);
    res.json({ data: dateList });
  });
});






// 获取错误单词排行榜前五名
app.get('/api/top-error-words', (req, res) => {
  const sql = `
    SELECT 
      w.id AS wordId,
      w.word,
      SUM(e.error_count) AS totalErrorCount
    FROM error_words_statistics e
    JOIN words w ON e.word_id = w.id
    GROUP BY w.id, w.word
    ORDER BY totalErrorCount DESC
    LIMIT 5;
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('查询错误单词排行榜失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({
      data: results
    });
  });
});

// 插入新单词接口（改成 /api/insertNewWords）
app.post('/api/insertNewWords', (req, res) => {
  const { word, definition, phonetic } = req.body;

  if (!word) {
    return res.status(400).json({ message: '单词(word)不能为空' });
  }

  const sql = 'INSERT INTO words (word, definition, phonetic) VALUES (?, ?, ?)';
  const values = [word, definition || null, phonetic || null];

  db.query(sql, values, (err, result) => {
    if (err) {
      console.error('插入单词失败:', err);
      return res.status(500).json({ message: '数据库错误', error: err });
    }
    res.status(201).json({ message: '单词插入成功', wordId: result.insertId });
  });
});

// 更新单词
app.post('/api/updateWord', (req, res) => {
  const { id, word, definition, phonetic } = req.body;

  if (!id) {
    return res.status(400).json({ message: '单词ID(id)不能为空' });
  }

  if (!word) {
    return res.status(400).json({ message: '单词(word)不能为空' });
  }

  if (word.length > 100) {
    return res.status(400).json({ message: '单词长度不能超过 100 个字符' });
  }

  if (phonetic && phonetic.length > 50) {
    return res.status(400).json({ message: '音标长度不能超过 50 个字符' });
  }

  const sql = 'UPDATE words SET word = ?, definition = ?, phonetic = ? WHERE id = ?';
  const values = [word, definition || null, phonetic || null, id];

  db.query(sql, values, (err, result) => {
    if (err) {
      console.error('更新单词失败:', err);
      return res.status(500).json({ message: '数据库错误', error: err });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到对应的单词ID' });
    }

    res.status(200).json({ message: '单词更新成功' });
  });
});
































// 读取 Excel 文件并返回加密数据
app.get('/data', (req, res) => {
  try {
    const filePath = path.join(__dirname, './Data/Data.xls');
    const workbook = xlsx.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const jsonData = xlsx.utils.sheet_to_json(worksheet, { header: 1 });
    const encryptedData = CryptoJS.AES.encrypt(JSON.stringify(jsonData), SECRET_KEY).toString();
    res.json({ success: true, data: encryptedData });
  } catch (error) {
    console.error('读取 Excel 失败:', error);
    res.status(500).json({ success: false, message: '服务器错误' });
  }
});

// AI 聊天接口，调用外部 API
app.post('/ai-chat', async (req, res) => {
  try {
    const data = req.body;
    // console.log('Request Body:', data);
    // 组装请求体
    const requestBody = {
      model: "4.0Ultra",
      messages: data.messages,
      stream: false,
      max_tokens: 1024
    };
    // 发送请求到 AI API
    const response = await axios.post(
      'https://spark-api-open.xf-yun.com/v1/chat/completions',
      requestBody,
      {
        headers: {
          'Authorization': 'Bearer joRSxASbPrOtUadecqgA:OVtblvXIkRXQZGDhUEyw',
          'Content-Type': 'application/json',
        }
      }
    );
    res.json(response.data);
  } catch (error) {
    console.error('Error:', error.response?.data || error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

// 翻译 API 封装接口
app.post('/api/translate', async (req, res) => {
  const { text, from, to } = req.body;

  // 参数校验
  if (!text || !from || !to) {
    return res.status(400).json({
      code: 400,
      data: null,
      msg: '参数缺失: text, from, to 必填',
    });
  }

  try {
    // 调用外部翻译接口
    const response = await axios.get('https://translate.appworlds.cn', {
      params: { text, from, to },
    });

    res.json(response.data); // 返回翻译结果
  } catch (error) {
    res.status(500).json({
      code: 500,
      data: null,
      msg: '翻译请求失败，请稍后重试',
    });
  }
});



// 启动服务器
app.listen(PORT, () => {
  console.log(`🚀 服务器运行在 http://localhost:${PORT}`);
});